package BaseDatos;

import BaseDatos.DAO.MySQLDAOFactory;
import BaseDatos.DAO.OcaDAO;
import Servidor.Modelo.Games.Game;
import Servidor.Modelo.Users.User;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * @author Angel Lopez
 */
public class OcaMySQLDAO implements OcaDAO{
    private Connection con;
    private boolean error = false;
    @Override
    public int insert_user(String nick, String pass, String email, int type_user) {
        int codigo = -1;
        int lineas_insertadas = 0;
        
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return -1;
            
            PreparedStatement stmtCP = (PreparedStatement) con.prepareStatement(QueriesMySQL.INSERT_USER);
            stmtCP.setString(1, nick);
            stmtCP.setString(2, pass);
            stmtCP.setString(3, email);
            stmtCP.setInt(4, type_user);

            lineas_insertadas = stmtCP.executeUpdate();
            
            // AQUI DEVOLVEMOS EL ULTIMO CODIGO INSERTADO, TENEMOS QUE HACER UNA NUEVA CONSULTA
            if(lineas_insertadas==0){
                error=true;
            }        
            else{
                Statement stmt = (Statement) con.createStatement();
                ResultSet rs = stmt.executeQuery(QueriesMySQL.SELECT_USER_LAST_INSERT);
                rs.next();
                codigo = rs.getInt(1);
            }
            
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error)){
                return -1;
            }
        }
        
        return codigo;
    }

    @Override
    public boolean delete_user(int id_user) {
        error = false;
        
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return false;
            
            PreparedStatement stmtCP = (PreparedStatement) con.prepareStatement(QueriesMySQL.DELETE_USER);

            stmtCP.setInt(1, id_user);

            stmtCP.executeUpdate();

        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error))
                return false;
        }
        
        return true;
    }

    @Override
    public int update_user(int id_user, String nick, String pass, String email, int type_user) {

        int lineas_actualizadas = 0;
        
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return -1;
            
            PreparedStatement stmtCP = (PreparedStatement) con.prepareStatement(QueriesMySQL.UPDATE_USER);
            stmtCP.setString(1, nick);
            stmtCP.setString(2, pass);
            stmtCP.setString(3, email);
            stmtCP.setInt(4, type_user);
            stmtCP.setInt(5, id_user);

            lineas_actualizadas = stmtCP.executeUpdate();
            
  
            
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error)){
                return -1;
            }
        }
        
        return lineas_actualizadas;
    }

    @Override
    public User select_user(int id_user) {
        User user_aux = new User();
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return null;
                
            PreparedStatement stmtCP = (PreparedStatement) con.prepareStatement(QueriesMySQL.SELECT_USER_ONE);
            stmtCP.setInt(1, id_user);
            
            ResultSet rs = stmtCP.executeQuery();
            
            while(rs.next()){
                
                user_aux.setId_user(rs.getInt("ID_USER"));
                user_aux.setNick(rs.getString("NICK"));
                user_aux.setPass(rs.getString("PASS"));
                user_aux.setEmail(rs.getString("EMAIL"));
                user_aux.setType_user(rs.getInt("TYPE_USER"));

            }
            
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error)){
                return null;
            }
        }
        
        return user_aux;
    }
    
    @Override
    public User select_user_by_nick(String nick) {
        User user_aux = new User();
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return null;
                
            PreparedStatement stmtCP = (PreparedStatement) con.prepareStatement(QueriesMySQL.SELECT_USER_ONE_NICK);
            
            stmtCP.setString(1, nick);
            
            ResultSet rs = stmtCP.executeQuery();
            
            while(rs.next()){
                
                user_aux.setId_user(rs.getInt("ID_USER"));
                user_aux.setNick(rs.getString("NICK"));
                user_aux.setPass(rs.getString("PASS"));
                user_aux.setEmail(rs.getString("EMAIL"));
                user_aux.setType_user(rs.getInt("TYPE_USER"));

            }
            
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error)){
                return null;
            }
        }
        
        return user_aux;
    }
    
    @Override
    public int select_user_login(String nick, String pass) {
        int type_user = -1;
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return -1;
                
            PreparedStatement stmtCP = (PreparedStatement) con.prepareStatement(QueriesMySQL.SELECT_USER_LOGIN);
            
            stmtCP.setString(1, nick);
            stmtCP.setString(2, pass);
            
            ResultSet rs = stmtCP.executeQuery();
            
            while(rs.next()){
                type_user = rs.getInt("TYPE_USER");
            }
            
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error)){
                return -1;
            }
        }
        
        return type_user;
    }

    
    
    

    @Override
    public ArrayList<User> select_list_users() {
        ArrayList<User> list_users = new ArrayList<User>();
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return null;
                
            Statement stmt = (Statement) con.createStatement();
            ResultSet rs = stmt.executeQuery(QueriesMySQL.SELECT_USERS);
            while(rs.next()){
                User user_aux = new User();
                user_aux.setId_user(rs.getInt("ID_USER"));
                user_aux.setNick(rs.getString("NICK"));
                user_aux.setPass(rs.getString("PASS"));
                user_aux.setEmail(rs.getString("EMAIL"));
                user_aux.setType_user(rs.getInt("TYPE_USER"));

                list_users.add(user_aux);
            }
            
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error)){
                return null;
            }
        }
        
        return list_users;
    }

    @Override
    public int insert_game(int id_owner, String name_game) {
        
        int codigo = -1;
        int lineas_insertadas = 0;
        
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return -1;
            
            PreparedStatement stmtCP = (PreparedStatement) con.prepareStatement(QueriesMySQL.INSERT_GAME);
            stmtCP.setInt(1, id_owner);
            stmtCP.setString(2, name_game);
            lineas_insertadas = stmtCP.executeUpdate();
            
            // AQUI DEVOLVEMOS EL ULTIMO CODIGO INSERTADO, TENEMOS QUE HACER UNA NUEVA CONSULTA
            if(lineas_insertadas==0){
                error=true;
            }        
            else{
                Statement stmt = (Statement) con.createStatement();
                ResultSet rs = stmt.executeQuery(QueriesMySQL.SELECT_GAME_LAST_INSERT);
                rs.next();
                codigo = rs.getInt(1);
            }
             
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error)){
                return -1;
            }
        }
        
        return codigo;
    }
    
    @Override
    public boolean delete_game(int id_game) {
        error = false;
        
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return false;
            
            PreparedStatement stmtCP = (PreparedStatement) con.prepareStatement(QueriesMySQL.DELETE_GAME);

            stmtCP.setInt(1, id_game);

            stmtCP.executeUpdate();

        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error))
                return false;
        }
        
        return true;
    }


    /**
     * @param id_game
     * @param id_winner
     * @return -1 if dont update
     */
    @Override
    public int update_game_finish(int id_game, int id_winner) {

        int lineas_actualizadas = -1;
        
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return -1;
            
            PreparedStatement stmtCP = (PreparedStatement) con.prepareStatement(QueriesMySQL.UPDATE_GAME_FINISH);

            
            stmtCP.setInt(1, id_winner);
            stmtCP.setInt(2, id_game);

            lineas_actualizadas = stmtCP.executeUpdate();
            
            // AQUI DEVOLVEMOS EL ULTIMO CODIGO INSERTADO, TENEMOS QUE HACER UNA NUEVA CONSULTA
            if(lineas_actualizadas==0)
                error=true;
                   
   
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error))
                return -1;
        }
        
        return lineas_actualizadas;
    }
    
    @Override
    public int update_game_finish_date(int id_game) {
        int lineas_actualizadas = -1;
        
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return -1;
            
            PreparedStatement stmtCP = (PreparedStatement) con.prepareStatement(QueriesMySQL.UPDATE_GAME_FINISH_DATE);

            stmtCP.setInt(1, id_game);

            lineas_actualizadas = stmtCP.executeUpdate();
            
            // AQUI DEVOLVEMOS EL ULTIMO CODIGO INSERTADO, TENEMOS QUE HACER UNA NUEVA CONSULTA
            if(lineas_actualizadas==0)
                error=true;
                   
   
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error))
                return -1;
        }
        
        return lineas_actualizadas;
    }
    

    @Override
    public ArrayList<Game> select_list_games() {
        ArrayList<Game> list_games = new ArrayList<Game>();
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return null;
                
            Statement stmt = (Statement) con.createStatement();
            ResultSet rs = stmt.executeQuery(QueriesMySQL.SELECT_GAMES);
            while(rs.next()){
                Game game_aux = new Game();
                game_aux.setId_game(rs.getInt("ID_GAME"));
                game_aux.setName_game(rs.getString("NAME_GAME"));
                game_aux.setId_owner(rs.getInt("ID_OWNER"));
                game_aux.setId_winner(rs.getInt("ID_WINNER"));
                game_aux.setDate_start(rs.getString("DATE_START"));
                game_aux.setDate_finish(rs.getString("DATE_FINISH"));
                game_aux.setActive(rs.getInt("ACTIVE"));
                list_games.add(game_aux);
            }
            
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error)){
                return null;
            }
        }
        
        return list_games;
    }
    
    @Override
    public ArrayList<Game> select_list_games_active() {
        ArrayList<Game> list_games = new ArrayList<Game>();
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return null;
                
            Statement stmt = (Statement) con.createStatement();
            ResultSet rs = stmt.executeQuery(QueriesMySQL.SELECT_ACTIVE_GAMES);
            while(rs.next()){
                Game game_aux = new Game();
                game_aux.setId_game(rs.getInt("ID_GAME"));
                game_aux.setName_game(rs.getString("NAME_GAME"));
                game_aux.setId_owner(rs.getInt("ID_OWNER"));
                game_aux.setId_winner(rs.getInt("ID_WINNER"));
                game_aux.setDate_start(rs.getString("DATE_START"));
                game_aux.setDate_finish(rs.getString("DATE_FINISH"));
                game_aux.setActive(rs.getInt("ACTIVE"));
                list_games.add(game_aux);
            }
            
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error)){
                return null;
            }
        }
        
        return list_games;
    }
    
    
    
    
    
    

    @Override
    public int insert_player(int id_game, int id_player) {

        int lineas_insertadas = 0;
        
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return -1;
            
            PreparedStatement stmtCP = (PreparedStatement) con.prepareStatement(QueriesMySQL.INSERT_PLAYER);
            stmtCP.setInt(1, id_game);
            stmtCP.setInt(2, id_player);

            lineas_insertadas = stmtCP.executeUpdate();
            
            if(lineas_insertadas==0)
                error=true;
            
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error)){
                return -1;
            }
        }
        
        return lineas_insertadas;
    }
    
    @Override
    public ArrayList<String> select_player() {
        ArrayList<String> list_player_game = new ArrayList<String>();
        try{
            con = MySQLDAOFactory.createConnection();
            if (con==null)
                return null;
                
            Statement stmt = (Statement) con.createStatement();
            ResultSet rs = stmt.executeQuery(QueriesMySQL.SELECT_PLAYERS);
            while(rs.next()){
                
                String linea = "ID GAME (" + rs.getInt("ID_GAME_PLAYED") + ") - ID USER (" + rs.getString("NICK") + ")" + " - JOIN (" + rs.getString("DATE_JOIN")+ ")";

                list_player_game.add(linea);
            }
                  
        }
        catch (SQLException e){System.out.println(e); error=true;}
        finally{
            if(!cerrarConexion()||(error)){
                return null;
            }
        }
        
        return list_player_game;
    }
    
    
    public boolean cerrarConexion(){
        try {
            if (con==null)
                return true;
            con.close();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(OcaMySQLDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }









    
}
